from typing import Union, Optional
from sqlalchemy import text
from config import session
from sqlmodel import SQLModel, Field, select


class Hero(SQLModel, table=True):
    __table_args__ = {'extend_existing': True}
    id: Union[int, None] = Field(default=None, primary_key=True)
    name: str = Field(index=True)  # 添加索引
    secret_name: str
    age: Union[int, None] = None


class SysUser(SQLModel, table=True):
    __tablename__ = 'sys_user'
    user_id: Optional[int] = Field(default=None, primary_key=True)
    user_name: str = Field(index=True)


def select_user_1():
    result = session.exec(text("SELECT * FROM hero")).all()
    print(result)


# select_user_1()

# 携带参数
def select_user_2():
    stmt = text("SELECT * FROM hero where name =:name")
    result = session.exec(stmt, params={"name": "Spider-Boy"}).all()
    print(result)


# select_user_2()

# 结果映射


def select_user_3():
    stmt = text("SELECT * FROM hero where name =:name")
    # mappings ：把结果映射成 dict类型
    result = session.exec(stmt, params={"name": "Spider-Boy"}).mappings().all()
    print("----> 输出的结果 ： ", result)
    result = [Hero(**item) for item in result]
    print(result)


# select_user_3()


def select_user_4():
    raw_sql = text("SELECT * FROM hero where name =:name")

    # 使用 select().from_statement() 包裹原生 SQL，并绑定模型
    stmt = select(Hero).from_statement(raw_sql)
    result = session.exec(stmt, params={"name": "Spider-Boy"}).all()
    print(result)

# select_user_4()
